package net.admin.db.item;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class CategoryDAO {
	private ResultSet rs;
	private Connection conn;
	private Statement stmt;
	
	public CategoryDAO() throws Exception{
		Context initCtx=new InitialContext();
		Context envCtx=(Context)initCtx.lookup("java:comp/env");
		DataSource ds=(DataSource)envCtx.lookup("jdbc/OracleDB");
		this.conn=ds.getConnection();
		this.stmt=conn.createStatement();
	}
	
	public ArrayList getCategoryList() throws Exception{
		ArrayList list = new ArrayList();
			rs=stmt.executeQuery("select category_name,category_code from category");
			String [] row = null;
			
			while(rs.next()){
				row = new String[2];
				row[0]=rs.getString(1);
				row[1]=rs.getString(2);
				list.add(row);
			}
		
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		
		return list;
	}
	public boolean registCategory(String admin_id, String category_nm) throws Exception{
		StringBuffer query = new StringBuffer();
		query.append("insert into category values ")
		.append("((select lpad(count(*)+1,3,'0') from category),")
		.append("'" + category_nm + "', sysdate,'" + admin_id + "')");
		System.out.println(query.toString());
		int insert_cnt = stmt.executeUpdate(query.toString());
		return insert_cnt==1?true:false;
	}
	public String getCategoryCode(String category_nm) throws Exception{
		StringBuffer query = new StringBuffer();
		query.append("select category_code from category where category_name='" + category_nm + "'");
		System.out.println(query.toString());
		rs = stmt.executeQuery(query.toString());
		rs.next();
		return rs.getString(1);
	}
	public void close(){
		try{
			if(rs!=null)rs.close();
			if(stmt!=null)stmt.close();
			if(conn!=null)conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}

